//-----------------------------------------------------------------------
// <copyright file="Execute.cs" company="Microsoft">
// Copyright (c) Microsoft Corporation.  All rights reserved.
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY 
// KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
// IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
// PARTICULAR PURPOSE.
// </copyright>
// <author>Andy Reeves</author>
// <email>andyr</email>
// <date>2004-03-23</date>
// <summary>Executes the Sql query specified.</summary>
//-----------------------------------------------------------------------
namespace Microsoft.Sdc.Tasks.Sql
{
    using System;
    using System.Globalization;
    using System.IO;
    using System.Data.SqlClient;
    using SdcSql = Microsoft.Sdc.Tasks.Configuration.SqlServer;
    using Microsoft.Build.Framework;

    /// <summary>
    /// Execute the SQL query or script against the specified server and database
    /// </summary>
    /// <remarks>
    /// <code><![CDATA[<Sql.Execute ServerName="serverName" DatabaseName="database" ConnectionString="oleDBConnectionString" CommandTimeout="timeout" />]]></code>
    /// <para>where:</para>
    /// <para><i>serverName</i></para>
    /// <para>The server to grant access to.</para>
    /// <para><i>databaseName</i></para>
    /// <para>The database on the specified server to grant access to.</para>
    /// <para>If you do not specify a server and database name you must specify a 
    /// <see cref="ConnectionString">connection string</see>.</para>
    /// <para><i>connectionString</i></para>
    /// <para>An OLE/DB connection string detailing the connection parameters required.</para>
    /// <para>If you do not specify a connection string you must specify a
    /// <see cref="ServerName">serverName</see> and <see cref="DatabaseName">databaseName</see></para>
    /// <para><i>timeout</i></para>
    /// <para>Maximum time allowed to perform the task (seconds)</para>
    /// <para><i>sql</i></para>
    /// <para>SQL command line to execute</para>
    /// <para><i>path</i></para>
    /// <para>Full qualified or wildcard path to script(s) to execute</para>
    /// <para><i>exclusions</i></para>
    /// <para>A list of files to exclude from execution if a wildcard path has been specified</para>
    /// <para><i>parameters</i></para>
    /// <para>A list of tokens to substitute when executing the specified SQL.</para>
    /// <para><i>retryFailures</i></para>
    /// <para>Flag indicating action to take on failed execution</para>
    /// </remarks>
    /// <example>
    /// <code><![CDATA[
    /// <Project>
    ///     <Target Name="Test" >
    ///         <Sql.DisconnectUsers
    ///             UserName="testUser"
    ///             ServerName="(local)"
    ///             DatabaseName="testDB"
    ///             CommandTimeout="60" />
    ///     </Target>
    /// </Project>
    /// 
    /// <Project DefaultTargets="Default" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    /// <Import Project="Microsoft.Sdc.Common.tasks"/>
    /// <ItemGroup>
    ///     <myString Include="true">
    ///         <name>@P1</name>
    ///         <value>'Hello'</value>
    ///     </myString>
    ///     <myInteger Include="true">
    ///         <name>@P2</name>
    ///         <value>342</value>
    ///     </myInteger>
    /// </ItemGroup>
    /// <Target Name="Default">
    ///     <Sql.Execute Path="SDCParameter.sql" ServerName="." DatabaseName="master" Parameters="@(myString);@(myInteger)"/>
    /// </Target>
    /// </Project>
    /// 
    /// Where SDCParameter.sql contains:
    /// DECLARE @Str1 NVARCHAR(100);
    /// DECLARE @Int1 INT;
    /// 
    /// SET @Str1 = @P1;
    /// SET @Int1 = @P2;
    /// 
    /// RAISERROR('Str1 is: %s', 0, 1, @Str1) WITH NOWAIT
    /// RAISERROR('Int1 is: %d', 0, 1, @Int1) WITH NOWAIT
    /// 
    /// 
    /// -- Replaced Text
    /// DECLARE @Str1 NVARCHAR(100);
    /// DECLARE @Int1 INT;
    /// 
    /// SET @Str1 = 'Hello'
    /// SET @Int1 = 342
    /// 
    /// RAISERROR('Str1 is: %s', 0, 1, @Str1) WITH NOWAIT
    /// RAISERROR('Int1 is: %d', 0, 1, @Int1) WITH NOWAIT
    /// 
    /// -- Result
    /// Str1 is: Hello
    /// Int1 is: 342
    /// 
    /// ]]></code>    
    /// </example>
    public class Execute : TaskBase
    {
        private string connectionString = String.Empty;
        private string serverName = String.Empty;
        private string databaseName = String.Empty;
        private int commandTimeout = 30;
        private string path = String.Empty;
        private string sql = String.Empty;
        private string[] exclusions = null;
        private ITaskItem[] parameters = null;
        private bool retryFailures = false;

        /// <summary>
        /// Set this property to define a database connection string. This
        /// property can be used instead of the combination of <see cref="Microsoft.Sdc.Tasks.Sql.Execute.ServerName"/> and
        /// <see cref="Microsoft.Sdc.Tasks.Sql.Execute.DatabaseName"/>.
        /// </summary>
        public string ConnectionString
        {
            get { return (this.connectionString ?? String.Empty); }
            set { this.connectionString = value; }
        }

        /// <summary>
        /// The SQL Server name.
        /// </summary>
        public string ServerName
        {
            get { return (this.serverName ?? String.Empty); }
            set { this.serverName = value; }
        }

        /// <summary>
        /// Name of the database that the script will be run against.
        /// </summary>
        public string DatabaseName
        {
            get { return (this.databaseName ?? String.Empty); }
            set { this.databaseName = value; }
        }

        /// <summary>
        /// Gets or sets the wait time in seconds before terminating the 
        /// attempt to execute a command and generating an error.
        /// </summary>
        /// <remarks>
        /// Has a default value of 30 seconds.
        /// </remarks>
        public int CommandTimeout
        {
            get { return this.commandTimeout; }
            set { this.commandTimeout = value; }
        }

        /// <summary>
        /// The location of the script file to execute. The path can include wild-card
        /// characters '*' and '?'.
        /// </summary>
        /// <remarks>
        /// This property can only be set if <see cref="Microsoft.Sdc.Tasks.Sql.Execute.Sql"/> is not set.
        /// </remarks>
        public string Path
        {
            get { return (this.path ?? String.Empty); }
            set { this.path = value; }
        }

        /// <summary>
        /// SQL command text to be executed.
        /// </summary>
        /// <remarks>
        /// This property can only be set if <see cref="Microsoft.Sdc.Tasks.Sql.Execute.Path"/> is not set.
        /// </remarks>
        public string Sql
        {
            get { return (this.sql ?? String.Empty); }
            set { this.sql = value; }
        }

        /// <summary>
        /// A hash table of exclusions that are keyed on the path of the script file(s) that are to be
        /// omitted from execution. The paths can include wild-card characters '*' and '?'. The value
        /// entries for the corresponding keys are ignored.
        /// </summary>
        public string[] Exclusions
        {
            get { return this.exclusions; }
            set { this.exclusions = value; }
        }

        /// <summary>
        /// Contains the names of parameters that need to be substituted with 
        /// their given values before the script is executed.
        /// </summary>
        public ITaskItem[] Parameters
        {
            get { return this.parameters; }
            set { this.parameters = value; }
        }

        /// <summary>
        /// If true, scripts that failed will be retried. Execution will stop
        /// when all of the scripts have executed successfully or when there
        /// is no improvement in the number of scripts that failed after the
        /// last iteration.
        /// </summary>
        public bool RetryFailures
        {
            get { return this.retryFailures; }
            set { this.retryFailures = value; }
        }

        /// <summary>
        /// Performs the action of this task which is to execute T-SQL scripts.
        /// </summary>
        protected override void InternalExecute()
        {
            #region Execute code

            SdcSql.ScriptExecutionInfo executionInfo = new SdcSql.ScriptExecutionInfo();

            executionInfo.ConnectionString = this.connectionString;
            executionInfo.ServerName = this.serverName;
            executionInfo.DatabaseName = this.databaseName;
            executionInfo.Path = this.path;
            executionInfo.Sql = this.sql;
            executionInfo.RetryFailures = this.retryFailures;
            executionInfo.CommandTimeout = this.CommandTimeout;

            // Load all parameter names and values into a hashtable
            Log.LogMessageFromResources(
                MessageImportance.High,
                "Sql.ExecuteScripts",
                ((executionInfo.Path.Length > 0) ? executionInfo.Path : executionInfo.Sql));

            if (null != this.parameters)
            {
                foreach (ITaskItem parameter in this.parameters)
                {
                    string name = parameter.GetMetadata("name");
                    if (executionInfo.SubstitutionParameters.ContainsKey(name))
                    {
                        throw new TaskException("Sql.DuplicateParam", name);
                    }
                    else
                    {
                        string paramValue = parameter.GetMetadata("value");
                        executionInfo.SubstitutionParameters.Add(name, paramValue);
                        Log.LogMessageFromResources(MessageImportance.Low, "Sql.SubstituteParam", name, paramValue);
                    }
                }
            }

            // Load all the names of script files to be excluded into a hashtable
            if (null != this.exclusions)
            {
                foreach (string exclusion in this.exclusions)
                {
                    string exclusionPath = exclusion;

                    // If no directory specified for the exclusion file,
                    // then use the directory of the SQL script file
                    if (exclusionPath.IndexOf(@"\") == -1)
                    {
                        int dirPos = executionInfo.Path.LastIndexOf(@"\");

                        if (dirPos != -1)
                        {
                            exclusionPath = string.Concat(executionInfo.Path.Substring(0, (dirPos + 1)), exclusionPath);
                        }
                    }

                    FileInfo fileInfo = new FileInfo(exclusionPath);

                    exclusionPath = fileInfo.FullName.ToLower(CultureInfo.InvariantCulture);
                    if (executionInfo.Exclusions.ContainsKey(exclusionPath))
                    {
                        throw new TaskException("Sql.DuplicateExclusion", exclusionPath);
                    }
                    else
                    {
                        executionInfo.Exclusions.Add(exclusionPath, String.Empty);
                        Log.LogMessageFromResources(MessageImportance.Low, "Sql.ExcludeFile", exclusionPath);
                    }
                }
            }

            SdcSql.ScriptExecutor.ScriptFileExecuted += this.ScriptExecuted;
            try
            {
                SdcSql.ScriptExecutor.Execute(executionInfo);
            }
            finally
            {
                SdcSql.ScriptExecutor.ScriptFileExecuted -= this.ScriptExecuted;
            }

            #endregion
        }

        /// <summary>
        /// Event handler called when a script has completed executing.
        /// </summary>
        /// <param name="sender">
        /// The sender of the event.
        /// </param>
        /// <param name="scriptInfo">
        /// Information about the script that was executed.
        /// </param>
        private void ScriptExecuted(object sender, SdcSql.ScriptFileEventArgs scriptInfo)
        {
            if (scriptInfo.ScriptFileInfo != null)
            {
                if (scriptInfo.Succeeded)
                {
                    Log.LogMessageFromResources(MessageImportance.Low, "Sql.ExecutedScript", scriptInfo.ScriptFileInfo.Name);
                }
                else
                {
                    Log.LogMessageFromResources(
                        MessageImportance.Low,
                        "Sql.ExecutedScriptFailed",
                        scriptInfo.ScriptFileInfo.Name,
                        scriptInfo.ExecutionException.Message);
                }
            }
            else
            {
                if (scriptInfo.SqlInfo != null)
                {
                    foreach (SqlError error in scriptInfo.SqlInfo)
                    {
                        Log.LogMessageFromResources(
                            MessageImportance.Normal,
                            "Sql.TraceInfo",
                            error.Message);
                    }
                }
            }
        }
    }
}